﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using PIM.API;
using System.Data;
using PIM.Utilities.DataAccess;
using System.Configuration;

/// <summary>
/// Summary description for PIMServices
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class PIMServices : System.Web.Services.WebService {
    //public const string ConnectionString = "SQLServer:Server=ANHHN;Database=PIM;UID=sa;PWD=anh123;";
    public string ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
    private PIMManager dbmanager = null;
    public PIMServices () {

        dbmanager = new PIMManager(ConnectionString);
        dbmanager.Open();
    }

    #region RecipeUser
    /// <summary>
    /// Get RecipeUser
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUser(string UserId)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", 0);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    /// <summary>
    /// Get GetRecipeUserByRecipeId
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUserByRecipeId(string UserId, string RecipeId)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", RecipeId);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    /// <summary>
    /// Get RecipeUser by RecipeId
    /// </summary>
    /// <param name="RecipeId"></param>
    /// <returns></returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUserByName(string RecipeName,string UserId)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeName", RecipeName);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUserByRecipeName", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    [WebMethod]
    public List<MealPlanRecipeIngredient> GetRecipeIngredientByMealPlanId(string MealPlanId)
    {
        List<MealPlanRecipeIngredient> lstMPRI = new List<MealPlanRecipeIngredient>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecepeIngredientByMealPlanId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlanRecipeIngredient recipeByMealPlan = new MealPlanRecipeIngredient();

                recipeByMealPlan.Description = dr["Description"].ToString();
                recipeByMealPlan.MealPlanDetailId = Convert.ToInt32(dr["MealPlanDetailId"].ToString());
                recipeByMealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanId"].ToString());
                recipeByMealPlan.MealPlanName = dr["MealPlanName"].ToString();
                recipeByMealPlan.RecipeId = Convert.ToInt32(dr["RecipeId"].ToString());
                recipeByMealPlan.RecipeIngredientUserId = Convert.ToInt32(dr["UserId"].ToString());
                recipeByMealPlan.RecipeName = dr["RecipeName"].ToString();
                recipeByMealPlan.Serving = Convert.ToInt32(dr["Serving"].ToString());

                lstMPRI.Add(recipeByMealPlan);
            }
        }

        return lstMPRI;
    }

    [WebMethod]
    public RecipeUser GetRecipeUserByNameAndUserId(string RecipeName, int UserId)
    {
        RecipeUser recipe = new RecipeUser();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeName", RecipeName);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUserByRecipeNameAndUserID", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                if (!string.IsNullOrEmpty(dr["isShared"].ToString()))
                    recipe.IsShared = Convert.ToBoolean(dr["isShared"]);
                else
                {
                    recipe.IsShared = false;
                }
            }
            return recipe;
        }

        return null;
    }


    /// <summary>
    /// Get RecipeUser by RecipeId
    /// </summary>
    /// <param name="RecipeId"></param>
    /// <returns></returns>
    [WebMethod]
    public RecipeUser GetRecipeUserByRecipe(int RecipeId)
    {
        RecipeUser _recipeUser = new RecipeUser();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", RecipeId);
        inputHtable.Add("UserId", "0");
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {

                _recipeUser.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                _recipeUser.Description = dr["Description"].ToString();
                _recipeUser.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                _recipeUser.RecDirection = dr["RecDirection"].ToString();
                _recipeUser.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                _recipeUser.RecipeName = dr["RecipeName"].ToString();
                _recipeUser.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                _recipeUser.Serving = Convert.ToInt32(dr["Serving"].ToString());
                _recipeUser.RecipeIngredientUser = GetRecipeIngredientUser(_recipeUser.RecipeID);
                _recipeUser.RecipeImage = dr["Image"].ToString();
                _recipeUser.IngredientName = dr["IngredientName"].ToString();
                _recipeUser.UserID = Convert.ToInt32(dr["Userid"].ToString());
                _recipeUser.UserName = dr["UserName"].ToString();
                if (!string.IsNullOrEmpty(dr["isShared"].ToString()))
                    _recipeUser.IsShared = Convert.ToBoolean(dr["isShared"]);
                else
                {
                    _recipeUser.IsShared = false;
                }
            }
        }

        return _recipeUser;
    }

    /// <summary>
    /// Update RecipeUser
    /// </summary>
    /// <param name="recipeId"></param>
    /// <param name="recipeName"></param>
    /// <param name="serving"></param>
    /// <param name="prepTime"></param>
    /// <param name="cookTime"></param>
    /// <param name="photo"></param>
    /// <param name="description"></param>
    /// <param name="recipeRating"></param>
    /// <param name="recDirection"></param>
    /// <returns></returns>
    public string UpdateRecipe(int recipeId, string recipeName, int userId ,int serving, int prepTime, int cookTime, 
        string photo, string description, double recipeRating, string recDirection, string recipeIngredient,bool isShared)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {

            return (dbAccess.UpdateRecipe(recipeId, recipeName, userId, serving, prepTime, cookTime, photo, description, 
                recipeRating, recDirection, recipeIngredient,isShared)).ToString();

        }

    }

    /// <summary>
    /// Delete RecipeUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <returns></returns>
    public string DeleteRecipe(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteRecipe(recipeID).ToString());
        }

    }

    /// <summary>
    /// Check RecipeUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <returns></returns>
    public string CheckRecipeOfMealPlan(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.CheckRecipeOfMealPlan(recipeID).ToString());
        }

    }

    /// <summary>
    /// Insert RecipeUser
    /// </summary>
    /// <param name="userId"></param>
    /// <param name="createUserId"></param>
    /// <param name="parentUserId"></param>
    /// <param name="recipeName"></param>
    /// <param name="serving"></param>
    /// <param name="prepTime"></param>
    /// <param name="cookTime"></param>
    /// <param name="photo"></param>
    /// <param name="description"></param>
    /// <param name="recipeRating"></param>
    /// <param name="recDirection"></param>
    /// <returns></returns>
    public int InsertRecipe(int userId, int createUserId, int parentUserId, string recipeName, int serving, int prepTime, int cookTime,
        string photo, string description, double recipeRating, string recDirection, string recipeIngredient, bool isShared)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return dbAccess.InsertRecipe(userId, createUserId, parentUserId, recipeName, serving, prepTime, cookTime
                , photo, description, recipeRating, recDirection, recipeIngredient, isShared);
        }

    }

    [WebMethod]
    public string InsertMyRecipe(string _recipeId, string _userId, string _oldUser)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.InsertMyRecipe(Convert.ToInt32(_recipeId), Convert.ToInt32(_userId), Convert.ToInt32(_oldUser)))
                return "OK";
            else
                return "NotOK";
        }
    }
    #endregion

    #region RecipeIngredientUser
    /// <summary>
    /// Insert RecipeIngredientUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <param name="ingredient"></param>
    /// <returns></returns>
    public string InsertRecipeIngredientUser(int recipeID, string ingredientIngredientName, int quantity, int units, int direction)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.InsertRecipeIngredientUser(recipeID, ingredientIngredientName,quantity,units,direction).ToString());
        }
    }

    /// <summary>
    /// Delete all recipeIngredientUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <returns></returns>
    public string DeleteAllRecipeIngredient(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteAllRecipeIngredient(recipeID).ToString());
        }

    }

    [WebMethod]
    private List<RecipeIngredientUser> GetRecipeIngredientUser(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {

            return dbAccess.getRecipeIngredientUser(recipeID);
        }

    }

    [WebMethod]
    public RecipeIngredientUser getRecipeIngredientUserByIngredientId(string IngredientId)
    {
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("IngredientId", IngredientId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_getIngredientUserById", inputHtable, ref outPutHtable, ref obj);
        RecipeIngredientUser RecipeIngredient = new RecipeIngredientUser();
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeIngredient.RecipeIngredientUserID = Convert.ToInt32(dr["RecipeIngredientUserID"].ToString());
                RecipeIngredient.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                RecipeIngredient.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                RecipeIngredient.Units = Convert.ToInt32(dr["Units"].ToString());
                RecipeIngredient.IngredientName = dr["IngredientName"].ToString();
                RecipeIngredient.Directions = dr["Directions"].ToString();
            }

        }
        return RecipeIngredient;

    }

    [WebMethod]
    public List<RecipeIngredientUser> getRecipeIngredientUserByRecipeID(string RecipeID)
    {
        List<RecipeIngredientUser> list = new List<RecipeIngredientUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeID", RecipeID);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeIngredientUser", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeIngredientUser RecipeIngredient = new RecipeIngredientUser();
                RecipeIngredient.RecipeIngredientUserID = Convert.ToInt32(dr["RecipeIngredientUserID"].ToString());
                RecipeIngredient.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                RecipeIngredient.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                RecipeIngredient.Units = Convert.ToInt32(dr["Units"].ToString());
                RecipeIngredient.IngredientName = dr["IngredientName"].ToString();
                RecipeIngredient.Directions = dr["Directions"].ToString();
                RecipeIngredient.RecipeImage = dr["Image"].ToString();
                RecipeIngredient.RecipeName = dr["RecipeName"].ToString();
                RecipeIngredient.Serving = dr["Serving"].ToString();
                list.Add(RecipeIngredient);
            }
            
        }
        return list;
    }

    [WebMethod]
    public List<RecipeIngredientUser> getRecipeIngredientUserByRecipeIDServing(string RecipeID, string Serving)
    {
        List<RecipeIngredientUser> list = new List<RecipeIngredientUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeID", RecipeID);
        inputHtable.Add("NewServing", Serving);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeIngredientUserServing", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeIngredientUser RecipeIngredient = new RecipeIngredientUser();
                RecipeIngredient.RecipeIngredientUserID = Convert.ToInt32(dr["RecipeIngredientUserID"].ToString());
                RecipeIngredient.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                RecipeIngredient.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                RecipeIngredient.Units = Convert.ToInt32(dr["Units"].ToString());
                RecipeIngredient.IngredientName = dr["IngredientName"].ToString();
                RecipeIngredient.Directions = dr["Directions"].ToString();
                RecipeIngredient.RecipeImage = dr["Image"].ToString();
                RecipeIngredient.RecipeName = dr["RecipeName"].ToString();
                RecipeIngredient.Serving = dr["Serving"].ToString();
                list.Add(RecipeIngredient);
            }

        }
        return list;
    }

    [WebMethod]
    public List<IngredientDept> getIngredientDept(string ingredientName)
    {
        List<IngredientDept> list = new List<IngredientDept>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("str", ingredientName);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetGloceryListWithDept", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                IngredientDept ingredient = new IngredientDept();
                ingredient.GroDept = dr["GroDept"].ToString();
                list.Add(ingredient);
            }

        }
        return list;
    }

    [WebMethod]
    public List<IngredientDeptDetail> getIngredientDeptDetail(string ingredientName,string GroDept)
    {
        List<IngredientDeptDetail> list = new List<IngredientDeptDetail>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("str", ingredientName);
        inputHtable.Add("GroDept", GroDept);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetGloceryListWithDeptDetail", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                IngredientDeptDetail ingredientDetail = new IngredientDeptDetail();
                ingredientDetail.GroDept = dr["GroDept"].ToString();
                ingredientDetail.ingre = dr["ingre"].ToString();
                list.Add(ingredientDetail);
            }

        }
        return list;
    }

    [WebMethod]
    public string DeleteIngredientUser(string _IngredientID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteIngredientUserByIngredientID(_IngredientID).ToString());
        }
    }


    #endregion


    #region PlanItMeal

    [WebMethod]
    public string InsertMyMealPlan(string _mealPlanId, string _userId, string _oldUser)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.InsertMyMealPlan(Convert.ToInt32(_mealPlanId), Convert.ToInt32(_userId), Convert.ToInt32(_oldUser)))
                return "OK";
            else
                return "NotOK";
        }
    }

    public int InsertPlanItMeal(int _userId, int _createdUserId, int _parentUserId, string _mealPlanName, bool _isPublished, bool _canCopy, double _mealPlanRating,
            int _avgCalPerDay, int _estCostPerDay, string _mealPlanPhoto, int _noOfReview,string _description)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.InsertMealPlan(_userId, _createdUserId, _parentUserId, _mealPlanName, _isPublished, _canCopy, _mealPlanRating, _avgCalPerDay, _estCostPerDay, _mealPlanPhoto, _noOfReview, _description));
        }
    }

    public bool InsertPlanItMealDetail(int _mealPlanID, int _mealTypeID, int _recipeID, int _groceryItemID, int _planTypeID, int _noOfServings,
            DateTime _dateCreated, string _dayOfWeek, string _userId)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.InsertMealPlanDetail(_mealPlanID, _mealTypeID, _recipeID, _groceryItemID, _planTypeID, _noOfServings, _dateCreated, _dayOfWeek, _userId));
        }
    }
    #endregion

    #region MealPlan

    [WebMethod]
    public MealPlan GetMealPlanByMealPlanId(string MealPlanId)
    {
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        MealPlan _MealPlan = new MealPlan();
        
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanByMealPlanId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                
                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
            }
        }
        return _MealPlan;
    }

    [WebMethod]
    public List<MealPlan> GetMealPlan(string MealPlanId, string UserId)
    {
        List<MealPlan> lstMealPlan = new List<MealPlan>();
        
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlan", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlan _MealPlan = new MealPlan();
                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                //_MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
                lstMealPlan.Add(_MealPlan);
            }
        }

        return lstMealPlan;
    }

    [WebMethod]
    public List<RecipeUser> GetRecipeUserByMealPlan(int MealPlanId)
    {
        List<RecipeUser> lRecipe = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanID", MealPlanId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanDetailInfoFromMealPlanID", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe = GetRecipeUserByRecipe(Convert.ToInt32(dr["RecipeID"]));
                
                lRecipe.Add(recipe);
            }
        }
        return lRecipe;
    }

    [WebMethod]
    public List<MealPlan> GetMealPlanByName(string MealPlanName,string UserId)
    {
        List<MealPlan> lstMealPlan = new List<MealPlan>();

        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanName", MealPlanName);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanByName", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlan _MealPlan = new MealPlan();
                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                lstMealPlan.Add(_MealPlan);
            }
        }

        return lstMealPlan;
    }
    [WebMethod]
    public string DeleteRecipeFromMealPlan(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteRecipeFromMealPlan(recipeID).ToString());
        }

    }

    [WebMethod]
    public string DeleteMealPlanDetail(int mealPlanID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteMealPlanDetail(mealPlanID).ToString());
        }

    }


    public string UpdateMealPlan( int MealPlanId, int UserID, int CreatedUserID, int ParentUserID, string MealPlanName, bool isPublished, bool canCopy,
        int NoOfReview, double MealPlanRating, int AvgCalPerDay, int EstCostPerDay, string MealPlanPhoto, string Description)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {

            return (dbAccess.UpdateMealPlan( MealPlanId, UserID, CreatedUserID, ParentUserID, MealPlanName, isPublished, canCopy,
                     NoOfReview, MealPlanRating, AvgCalPerDay, EstCostPerDay, MealPlanPhoto, Description)).ToString();

        }

    }

    public string DeletMealPlan(int mealPlanID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeletMealPlan(mealPlanID).ToString());
        }
    }


    #endregion

    #region Glocery

    /// <summary>
    /// 
    /// </summary>
    /// <param name="IngredientName"></param>
    /// <param name="UserID"></param>
    /// <returns></returns>
    [WebMethod]
    public List<IngredientList> addIngredientList(string IngredientName, string UserID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.InsertIngredient(Convert.ToInt32(UserID), IngredientName))
                return getIngredientList(UserID);
            else
                return null;
        }
    }

    public List<IngredientList> getIngredientList(string UserID)
    {
        string sql = string.Format("select * from [IngredientListUser] where CAST(FLOOR(CAST([CreateDate] AS FLOAT )) AS DATETIME)=CAST(FLOOR(CAST(getdate() AS FLOAT )) AS DATETIME) and UserID=" + UserID);
        List<IngredientList> list = new List<IngredientList>();
        try
        {
            DataTable dt = new DataTable();
            dt = dbmanager.GetData(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    IngredientList ingredient = new IngredientList();
                    ingredient.IngredientID = Convert.ToInt32(r["IngredientID"]);
                    ingredient.UserID = Convert.ToInt32(r["UserID"]);
                    ingredient.Long_Desc = r["Long_Desc"].ToString();
                    list.Add(ingredient);
                }
                return list;
            }
        }
        catch (Exception)
        {

        }
        return null;
    }

    #endregion

    #region Authentication

    [WebMethod(EnableSession=true)]
    public string Login(string _userName, string _passWord)
    {
        return signin(_userName,_passWord);
    }

    [WebMethod(EnableSession = true)]
    public string Logout()
    {
        if (Session["UserName"] != null)
            Session["UserName"] = null;
        if (Session["Userid"] != null)
            Session["Userid"] = null;

        return "";
    }

    [WebMethod(EnableSession = true)]
    private string signin(string _userName, string _passWord)
    {
        string sql = "";
        sql = "select Userid, UserName, Password from Users where UserName='" + _userName + "' and Password='" + _passWord + "'";
        try
        {
            DataTable _dt = dbmanager.GetData(sql);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                Session.Add("UserName", _dt.Rows[0]["UserName"]);
                Session.Add("Userid", _dt.Rows[0]["Userid"]);
                return _dt.Rows[0]["UserName"].ToString();
            }
            else
                return "";
        }
        catch
        {
            return "";
        }
    }

    [WebMethod(EnableSession = true)]
    public string CreateNewMember(string _userName, string _password)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.CreateNewUser(_userName, _password))
                return signin(_userName,_password);
            else
                return "NotOK";
        }
    }

    [WebMethod]
    public Users GetUserById(string _userId)
    {
        Users us = new Users();


        //List<RecipeUser> lRecipe = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("UserId", _userId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_getUserByUserId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            us.Password = _dt.Rows[0]["Password"].ToString();
            us.UserName = _dt.Rows[0]["UserName"].ToString();
            us.UserID = _dt.Rows[0]["Userid"].ToString();
        }
        return us;
    }

    [WebMethod]
    public string ChangePassword(string _userId,string _password)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.ChangPassword(_userId, _password))
                return "OK";
            else
                return "NotOK";
        }
    }

    #endregion


    #region
    [WebMethod]
    public string Rating(int objectTypeID,int objectID,int rating)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.Rating(objectTypeID,objectID,rating).ToString());
        }
    }

    #endregion
}
